﻿CREATE PROCEDURE [dbo].[s_ExportFromMegasys]
	@MsID varchar(15)
AS
SET NOCOUNT ON;

DECLARE @Account int SET @Account = RIGHT(@MsID,6)

EXEC sh_Debug_PRINT 's_ExportFromMegasys $0',@MsID
IF NOT @MsID LIKE '___-%' BEGIN	RAISERROR('Megasys ID has incorrect format',16,1)	RETURN END

IF EXISTS(SELECT * FROM t_Loan WHERE MsID = @MsID)BEGIN	RAISERROR('Megasys account %s has already been exported.',16,1,@MsID) RETURN END

DECLARE @LoanID varchar(50),@XFA_VIN varchar(18),@VC bit,@MoveForward bit

--IF (SELECT COUNT(*) FROM v_ExportFromMegasys WHERE XFA_CLASSACCOUNT = @MsID) > 1 BEGIN RAISERROR('More then one record returned for MsID [%s] from v_ExportFromMegasys',16,1,MsID) RETURN 0 END

SELECT @XFA_VIN = XFA_VIN,@VC = VC FROM v_ExportFromMegasys WHERE XFA_CLASS = 10 AND XFA_ACCOUNT = @Account
IF @XFA_VIN IS NULL BEGIN	RAISERROR('No records found for MsID [%s] in v_ExportFromMegasys',16,1,@MsID)RETURN 0 END
IF @VC = 0 BEGIN RAISERROR('VIN [%s] in Megasys account [$s] is incorrect.',16,1,@XFA_VIN,@MsID) RETURN 0 END

EXEC sh_Debug_PRINT 'BEGIN TRAN s_ExportFromMegasys (@VIN=$0',@XFA_VIN
BEGIN TRAN s_ExportFromMegasys

IF NOT EXISTS(SELECT * FROM t_Car WHERE VIN = @XFA_VIN)INSERT INTO t_Car(VIN) VALUES(@XFA_VIN)
UPDATE t_Car SET Year = XFA_YEAR, Make = XFA_MAKE, Model = XFA_MODEL, Color = XFA_COLOR, Mileage = XFA_MILEAGE
FROM t_Car c CROSS JOIN v_ExportFromMegasys ms
WHERE c.VIN = @XFA_VIN AND ms.XFA_CLASS = 10 AND ms.XFA_ACCOUNT = @Account

--===============================================================================
IF (SELECT COUNT(*) FROM t_CarInfo WHERE VIN IN (@XFA_VIN,RIGHT(@XFA_VIN,6))) > 1 BEGIN
	ROLLBACK TRAN s_ExportFromMegasys
	RAISERROR('VIN [%s] is used more than once.',16,1,@XFA_VIN) RETURN 0
END
UPDATE t_CarInfo SET VIN = NULL WHERE VIN IN (@XFA_VIN,RIGHT(@XFA_VIN,6))

/**/EXEC sh_Debug_PRINT 'INSERT INTO t_Loan(MsID,DealerSalesPersonID,MenuID)'
INSERT INTO t_Loan(MsID,DealerSalesPersonID,MenuID)
SELECT TOP 1  XFA_CLASSACCOUNT,DealerSalesPersonID,84
FROM v_ExportFromMegasys WHERE XFA_CLASS = 10 AND XFA_ACCOUNT = @Account
IF @@ERROR <> 0 BEGIN ROLLBACK TRAN s_ExportFromMegasys RETURN 0 END

SET @LoanID = IDENT_CURRENT('t_Loan')

/**/EXEC sh_Debug_PRINT 'UPDATE t_LoanInfo SET ContractDate = XFA_OPENDT,@MoveForward = 1 FROM t_LoanInfo li'
UPDATE t_LoanInfo SET ContractDate = XFA_OPENDT,@MoveForward = 1 FROM t_LoanInfo li
INNER JOIN t_Loan l ON l.ID = li.LoanID
INNER JOIN v_ExportFromMegasys em ON l.Ms_CLASS = em.XFA_CLASS AND l.Ms_ACCOUNT = em.XFA_ACCOUNT
WHERE em.XFA_CLASS = 10 AND em.XFA_ACCOUNT = @Account
IF @@ERROR <> 0 BEGIN ROLLBACK TRAN s_ExportFromMegasys RETURN 0 END

UPDATE t_CarInfo SET VIN = @XFA_VIN WHERE LoanID = @LoanID

IF @@ERROR <> 0 BEGIN ROLLBACK TRAN s_ExportFromMegasys RETURN 0 END
-- Tracker

--SELECT * FROM t_Tracker_Car WHERE VIN = @XFA_VIN
--SELECT * FROM vhist_t_Tracker_Car WHERE VIN = @XFA_VIN

IF EXISTS(SELECT * FROM t_Tracker_Car WHERE VIN = @XFA_VIN)
	/**/EXEC sh_Debug_PRINT 'VIN $0 exists.',@XFA_VIN
ELSE BEGIN
	DECLARE @TrackerIDs varchar(512),@SQL varchar(512)
	SET @SQL = 'SELECT DISTINCT TrackerID FROM v_ExportFromMegasys WHERE XFA_CLASS = 10 AND XFA_ACCOUNT = '+CONVERT(varchar,@Account)+' AND TrackerID > '''''
	EXEC sp__Join @SQL,',',@TrackerIDs OUTPUT
EXEC sh_Debug_Print 'Tracker(s):$0',@TrackerIDs
	IF @TrackerIDs > '' AND
		 EXISTS(SELECT * FROM t_Tracker WHERE ID IN (SELECT DISTINCT TrackerID FROM v_ExportFromMegasys	WHERE XFA_CLASS = 10 AND XFA_ACCOUNT=@Account) AND StatusID = 80)
BEGIN		--IF EXISTS(SELECT * FROM t_TrackerRequest WHERE LoanID = @LoanID AND StatusID = 0)BEGIN
		DECLARE @TrackerRequestID int,@TRLink nvarchar(1024)
EXEC sh_Debug_Print 'EXEC @TrackerRequestID = s_TrackerRequest_New $0',@XFA_VIN
		EXEC @TrackerRequestID = s_TrackerRequest_New @XFA_VIN
		IF @@ERROR <> 0 BEGIN ROLLBACK TRAN s_ExportFromMegasys RETURN 0 END
		
		IF EXISTS(SELECT * FROM t_TrackerRequest WHERE ID = @TrackerRequestID AND StatusID <> dbo.TrackerRequestStartID())BEGIN
			SET @TRLink = dbo.ShowTrackerRequest(@TrackerRequestID)
			RAISERROR('VIN [%s] has pending tracker request [%d].
Finish or cancel it before exporting [%s].
%s
',16,1,@XFA_VIN,@TrackerRequestID,@MsID,@TRLink)
			ROLLBACK TRAN s_ExportFromMegasys
			RETURN 0
		END

		EXEC sp__Context_WorkFlow_System 1
EXEC sh_Debug_PRINT 'UPDATE t_TrackerRequest SET StatusID=55/*55->Pending Installation*/,DealerID=ci.DealerID,TechnicianID=-1'
		UPDATE t_TrackerRequest SET StatusID=55/*55->Pending Installation*/,DealerID=ci.DealerID,TechnicianID=-1
		FROM t_TrackerRequest tr INNER JOIN v_CarInfo ci ON ci.VIN = tr.VIN
		WHERE tr.ID = @TrackerRequestID
		IF @@ERROR <> 0 BEGIN ROLLBACK TRAN s_ExportFromMegasys RETURN 0 END
		EXEC sp__Context_WorkFlow_System 0

EXEC sh_Debug_PRINT 'EXEC s_Tracker_InHouse @TrackerIDs'
		EXEC s_Tracker_InHouse @TrackerIDs
		IF @@ERROR <> 0 BEGIN ROLLBACK TRAN s_ExportFromMegasys RETURN 0 END

EXEC sh_Debug_PRINT 'EXEC s_Technician_Tracker_InHands -1,@TrackerIDs'
		EXEC s_Technician_Tracker_InHands -1,@TrackerIDs
		IF @@ERROR <> 0 BEGIN ROLLBACK TRAN s_ExportFromMegasys RETURN 0 END

EXEC sh_Debug_PRINT 'EXEC s_Tracker_Car_Install @TrackerRequestID,@TrackerIDs'
		EXEC s_Tracker_Car_Install @TrackerRequestID,@TrackerIDs
		IF @@ERROR <> 0 BEGIN ROLLBACK TRAN s_ExportFromMegasys RETURN 0 END

	END
END -- end of Tracker

EXEC sh_Debug_PRINT 's_ExportFromMegasys_AddOn @LoanID'
EXEC s_ExportFromMegasys_AddOn @LoanID

--IF @MoveForward = 1 BEGIN	EXEC s_MenuIDEx @LoanID,1,1	IF @@ERROR <> 0 BEGIN ROLLBACK TRAN s_ExportFromMegasys RETURN 0 END END

EXEC sh_Debug_PRINT 'COMMIT TRANSACTION s_ExportFromMegasys'
COMMIT TRANSACTION s_ExportFromMegasys

RETURN @LoanID




